SQL

对数据库进行管理的语言

分类：

• 数据查询语言 DQL (Data Query Language)
  select / where / order by / group by / having
• 数据定义语 DDL (Data Definition Language)
  create / alter / drop
• 数据操作语言 DML (Data Manipulation Language)
  insert / update / delete
• 事务处理语言 TPL (Transaction Process Language)
  commit / rollback
• 数据控制语言 DCL (Data Control Language)
  grant / revoke
  相关概念

MYSQL安装

打开installer -> 选择developr default -> 显示excute就点excute，没有就next & no -> 等待安装 -> 设置密码

MYSQL备份

mysqldump -h服务器名 -u用户名 -p密码 --routines --default-character-set=utf8 数据库名 > 文件位置/文件名.sql
复制

数据类型

Text 类型

• 需要加'单引号'

Number 类型

• 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
• tinyint读取的时候0转换成true，1转换成false，解决：select 字段名*1

Time 类型

• 如果在时间上要超过 Linux 时间的，或者想要使用自动插入时间或者自动更新时间功能的，timestamp 会随时区变化而变化，服务器时区不一样，用timestamp。
• 如果只是想表示年、日期、时间的还可以使用 year、date、time，它们分别占据 1、3、3 字节，用datetime

数据库表字段类型规范

• 用尽量少的存储空间来存数一个字段的数据;
• 例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);
• IP地址最好使用int类型;
• 固定长度的类型最好使用char,例如：邮编;
• 能使用tinyint就不要使用smallint,int;
• 最好给每个字段一个默认值,最好不能为null;

内置函数

生日

ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422)
复制

表内数据增删改查

增删改查

最基础、最实用的功能

查询 SELECT

SELECT 列名 FROM 表明 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 (ASC/DESC) LIMIT 起始行，总条数
复制

普通查询

SELECT 列名 FROM 表名
复制

表名、列名命名

表名/列名 (AS) 名称
复制

• 表名、列名之间用逗号分格，不用加括号

查询结果去重

DISTINCT 列名
复制

排序查询

依据单列

SELECT 列名 FROM 表名 ORDER BY 列名 ASC/DSC
复制

• ASC可以不写

依据多列

SELECT 列名 FROM 表名 ORDER BY 列名 ASC/DESC , 列名 ASC/DCS
复制

• 优先级从前往后

条件查询

等值/不等值查询

=，>，<，>=，<=，!=/<>

逻辑判断

AND，OR，NOT

区间判断

BETWEEN AND
闭区间，两个数都包含，必须小值在前，大值在后

NULL值判断

列名 IS/IS NOT NULL
复制

枚举查询

列名 IN(值1，值2，值3)
复制

模糊查询

• 单个任意字符

列名 LIKE 'A_'
复制

• 任意长度字符

列名 LIKE 'A%'
复制

长度不大于6个的字符串

NOT (列名 LIKE '______%')
复制

分支结构查询

CASE
	WHEN 条件1 THEN 结果1
	……
	ELSE 结果
END
复制

时间查询

SELECT 时间函数(参数列表)
复制

字符串查询

SELECT 字符串函数([参数列表])
复制

聚合函数

SELECT 聚合函数(列名) FROM 表名
复制

分组查询

SELECT 列名 FROM 表名 GROUP BY 列名
复制

• 计算聚合函数的结果
• 列名只包含分组依据列或者聚合函数列

分组过滤查询

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则
复制

• 过滤规则：聚合函数

限定查询(分页的应用)

SELECT 列名 FROM 表名 LIMIT 起始行, 查询行数
复制

• 第一行是0

子查询

SELECT 列名 FROM 表名 WHERE EXISTS(子查询)
SELECT 列名 FROM 表名(子查询) WHERE 条件
复制

合并查询

SELECT * FROM 表名1 UNION(去重)/UNION ALL(不去重) SELECT * FROM 表名2
复制

• 列数必须相同，类型可以不同

表连接查询

SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件
复制

表链接查询

删除 DELETE

DELETE FROM 表名 WHERE 条件
复制

修改 UPDATE

UPDATE 表名 SET 列1=值1,列2=值2 WHERE 条件
复制

增加 INSERT

INSERT INTO 表名(列1,列2..) VALUES(值1,值2)
复制

清空 TRUNCATE

清空表的数据

TRUNCATE TABLE 表名
复制

表的操作

创建表

基本语句

CREATE TABLE 表名(
	列名 数据类型[约束],
	列名 数据类型[约束],
	列名 数据类型[约束],
	……
)CHARSET=utf8
复制

优化

主键约束 PRIMARY KEY

不能为 NULL

CREATE TABLE 表名(
	列名 数据类型,
	PRIMARY KEY(列名)
	……
)CHARSET=utf8
复制

唯一约束 UNIQUE

可以为 NULL

CREATE TABLE 表名(
	列名 数据类型 UNIQUE,
	……
)CHARSET=utf8
复制

自动增长列 AUTO_INCREAMENT

必须为主键，数值自动 + 1

CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
	……
)CHARSET=utf8
复制

• 设置初始值：ALTER TABLE 表名 AUTO_INCREMENT=初始值

自动增长列 IDENTITY(M, N)

数值从 M 开始，每次增长 N

CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY IDENTITY(M,N),
	……
)CHARSET=utf8
复制

非空约束 NOT NULL

CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
	……
)CHARSET=utf8
复制

默认值约束 DEFAULT 值

CREATE TABLE 表名(
	列名 数据类型 DEFAULT 值,
	……
)CHARSET=utf8
复制

外键约束 FOREIGN KEY

引用名:fk_引用表名_引用列名
删除时先删除引用表再删除被引用表

CREATE TABLE 表名(
	列名 数据类型,
	CONSTRAINT 引用名 FOREIGN KEY(列名) REFERENCES 被引用表名(被引用列名) ON DELETE/UPDATE
CASCADE/SET NULL,
	……
)CHARSET=utf8
复制

• ON DELETE/UPDATE
  在删除时/更新时的操作
• NULL、RESTRICT、NO ACTION
  删除：从表记录不存在时，主表才可以删除。删除从表，主表不变
  更新：从表记录不存在时，主表才可以更新。更新从表，主表不变
• CASCADE
  删除：删除主表时自动删除从表。删除从表，主表不变
  更新：更新主表时自动更新从表。更新从表，主表不变
• SET NULL
  删除：删除主表时自动更新从表值为 NULL。删除从表，主表不变
  更新：更新主表时自动更新从表值为 NULL。更新从表，主表不变

索引

加快查询速度，但数值必须唯一，并且条件限定时必须使用

索引和主键的区别

主键索引与唯一索引的区别
1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
3. 唯一性索引列允许空值，而主键列不允许为空值。
4. 主键列在创建时，已经默认为空值 ++    唯一索引了。
5. 一个表最多只能创建一个主键，但可以创建多个唯一索引。
6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
7. 主键可以被其他表引用为外键，而唯一索引不能。

普通索引

无页数条件

唯一索引

可以出现空值，但是只能出现一次

CREATE UNIQUE INDEX indexName ON table(column(length))
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
DROP INDEX indexName ON tableName(column(length))
复制

建表索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);
复制

组合索引

类似用户名和密码之间需要写组合索引
最左边的字段必须出现，否则索引失效

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
复制

全文索引

全文索引

索引类型

create index indexName using indexType on table(column(length))
复制

B-Tree

二分树，建议用数字当索引，并最好是自动增长列，维护成本低

HASH

hash函数随机访问，只能用于in或者=，其他无效
InnoDB不支持(自带了)

修改 ALTER

基本语句

ALTER TABLE 表名 操作
复制

添加列

ALTER TABLE 表名 ADD 列名 类型
复制

修改列类型

ALTER TABLE 表名 MODIFY 列名 类型
复制

删除列

ALTER TABLE 表名 DROP 列名
复制

修改列名

ALTER TABLE 表名 CHANGE 原列名 列名 类型
复制

添加外键

alter table 表名 add constraint FK_ID foreign key(你的外键字段名) 
REFERENCES 外表表名(对应的表的主键字段名) 
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
复制

修改表名

ALTER TABLE 表名 RENAME 新表名
复制

删除表

DROP TABLE 表名
复制

数据库的操作

创建数据库

CREATE DATABASE 数据库名
复制

展示数据库

SHOW DATABASES
复制

使用数据库

USE 数据库名
复制

删除数据库

DROP DATABASE 数据库名
复制

事务

事务

权限管理

创建用户

CREATE USER 用户名 IDENTIFIDE BY '密码'
复制

授权

GRANT privileges ON 数据库.表 TO 用户名/public(所有人)
复制

• privileges：
  • update
  • select
  • insert
  • delete
  • all(update、select、insert、delete)

撤销授权

账户需要重新连接数据库才会生效

REVOKE privileges ON 数据库.表 FROM 用户名
复制

删除用户

账户需要重新连接数据库才会生效

DROP USER 用户名
复制

视图

视图

关系代数

关系代数

ER 图

ER图

范式

范式